package com.gjzt.business.contract.service;

import java.io.File;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.eastdigit.servlet.ReqBean;
import com.eastdigit.system.model.BusinessException;
import com.eastdigit.system.model.Constants;
import com.eastdigit.system.model.SqlBuilder;
import com.eastdigit.system.service.BaseService;
import com.eastdigit.util.DateUtil;
import com.eastdigit.util.ExcelReader;
import com.eastdigit.util.StringUtil;
import com.gjzt.business.contract.inter.ContractMapper;
import com.gjzt.business.contract.inter.ContractResourceMapper;
import com.gjzt.business.contract.model.Contract;
import com.gjzt.business.contract.model.ContractResource;
import com.gjzt.business.customer.inter.CustomerMapper;
import com.gjzt.business.customer.model.Customer;
import com.gjzt.business.media.inter.MediaResourceMapper;
import com.gjzt.business.media.model.MediaResource;
import com.gjzt.business.media.model.MediaType;
import com.gjzt.business.media.service.MediaBusService;
import com.gjzt.business.media.service.MediaResourceService;
import com.gjzt.business.media.service.MediaStopService;
import com.gjzt.business.media.service.MediaTypeService;
import com.gjzt.business.utils.Constant;

@Service
public class ContractService extends BaseService {

    @Autowired
    private ContractMapper contractMapper;

    @Autowired
    private MediaBusService mediaBusService;

    @Autowired
    private MediaStopService mediaStopService;

    @Autowired
    private MediaResourceService mediaResourceService;

    @Autowired
    private MediaResourceMapper mediaResourceMapper;

    @Autowired
    private ContractResourceMapper contractResourceMapper;

    @Autowired
    private CustomerMapper customerMapper;

    @Autowired
    private MediaTypeService mediaTypeService;

    /**
     * 查询
     * 
     * @param reqBean
     * @return
     */
    public JSONObject findContracts(ReqBean reqBean) {
        SqlBuilder sqlBuilder = null;
            sqlBuilder = new SqlBuilder(
                    "SELECT a.CONTRACT_ID,a.CONTRACT_AMOUNT,a.AMOUNT_RECEIVABLES,a.AMOUNT_RECEIVED,a.CONTRACT_NO,a.CONTRACT_TITLE,a.CONTRACT_DATE,"
                            + "a.CONTRACT_FILE,a.SOURCE_TYPE,ac.* FROM `AD_CONTRACT` a LEFT JOIN AD_CUSTOMER ac ON "
                            + "ac.CUSTOMER_ID = a.CUSTOMER_ID where 1=1 ")
                    .addWhereLike2("a.CONTRACT_NO", reqBean.getInfo("contractNo"))
                    .addWhere("a.SOURCE_TYPE", reqBean.getInfo("sourceType"))
                    .addWhere("a.CUSTOMER_ID", reqBean.getInfo("customerId"));
        return this.queryTable(sqlBuilder, reqBean.getPageSize(), reqBean.getPageIndex());
    }

    public JSONObject findContract(String contractId) {
        Contract contract = contractMapper.selectByPrimaryKey(contractId);
        return contract == null ? null : contract.toJsonObject();
    }

    public JSONObject findContractWidthCustomer(String contractId) {
        JSONObject obj = findContract(contractId);
        if (obj != null) {
            String customerId = obj.getString("customerId");
            Customer customer = customerMapper.selectByPrimaryKey(customerId);
            customer = customer == null ? new Customer() : customer;
            obj.put("customer", customer.toJsonObject());
        }

        return obj;
    }

    /**
     * 新增
     * 
     * @param reqBean
     */
    public JSONObject addContract(ReqBean reqBean) {
        Contract contract = getContract(reqBean);
        contract.setContractId(intIncrementId("AD_CONTRACT", "CONTRACT_ID"));
        contractMapper.insert(contract);
        if (StringUtil.isNotEmpty(reqBean.getInfo("importFilePath"))) {
            this.contractExcel(contract.getContractId(), reqBean.getInfo("importFilePath"), "");
        }
        return contract.toJsonObject();
    }

    /**
     * 修改
     * 
     * @param reqBean
     */
    public void updateContract(ReqBean reqBean) {
        String contractId = reqBean.getInfo("contractId");
        if (StringUtil.isEmpty(contractId)) {
            throw new BusinessException("获取主键失败");
        }
        Contract contract = getContract(reqBean);
        contractMapper.updateByPrimaryKeySelective(contract);
        if (StringUtil.isNotEmpty(reqBean.getInfo("importFilePath"))) {
            this.contractExcel(contract.getContractId(), reqBean.getInfo("importFilePath"), "");
        }
    }

    /**
     * 删除
     * 
     * @param reqBean
     */
    public void removeContract(ReqBean reqBean) {
        String contractId = reqBean.getInfo("contractId");
        if (StringUtil.isEmpty(contractId)) {
            throw new BusinessException("获取主键失败");
        }
        contractResourceMapper.deleteByContractId(contractId);
        contractMapper.deleteByPrimaryKey(contractId);
    }

    /**
     * 删除合同明细
     * 
     * @param reqBean
     */
    public void removeContractResource(String recordId) {
        if (StringUtil.isEmpty(recordId)) {
            throw new BusinessException("获取主键失败");
        }
        contractResourceMapper.deleteByPrimaryKey(recordId);
    }

    /**
     * 从表单获取对象
     * 
     * @param reqBean
     * @return
     */
    private Contract getContract(ReqBean reqBean) {
        Contract contract = new Contract();

        contract.setContractId(reqBean.getInfo("contractId"));
        contract.setContractNo(reqBean.getInfo("contractNo"));
        contract.setContractTitle(reqBean.getInfo("contractTitle"));
        contract.setCustomerId(reqBean.getInfo("customerId"));
        contract.setContractDate(DateUtil.parse(reqBean.getInfo("contractDate")));
        contract.setContractAmount(reqBean.getInfoDouble("contractAmount"));
        contract.setContractFile(reqBean.getInfo("contractFile"));
        contract.setSourceType(reqBean.getInfo("sourceType"));
        contract.setAmountReceivables(reqBean.getInfoDouble("amountReceivables"));
        contract.setAmountReceived(reqBean.getInfoDouble("amountReceived"));
        return contract;
    }

    /**
     * 查询合同明细
     * 
     * @param reqBean
     * @return
     */
    public JSONArray queryResources(String contractId) {
        if (StringUtils.isBlank(contractId)) {
            throw new BusinessException("缺少参数!");
        }
        StringBuffer sql = new StringBuffer("SELECT a.*");
        sql.append(" FROM AD_CONTRACT_RESOURCE a ");
        sql.append(" LEFT JOIN MEDIA_RESOURCE r on a.RESOURCE_ID = r.RESOURCE_ID");

        sql.append(" where a.CONTRACT_ID = '" + contractId + "'");
        sql.append(" order by r.BUS_ID,r.STOP_ID, r.MEDIA_TYPE ");
        JSONArray result = this.query(sql.toString());
        for (int i = 0; i < result.size(); i++) {
            JSONObject cr = result.getJSONObject(i);
            String resourceId = cr.getString("resourceId");
            MediaResource resource = mediaResourceService.getMediaResource(resourceId);
            cr.put("resourceInfo", resource.toJsonObject());
            String busId = resource.getBusId();
            String stopId = resource.getStopId();
            if (!StringUtil.isEmpty(busId)) {
                cr.put("busInfo", mediaBusService.getMediaBusInfo(busId));
            } else if (!StringUtil.isEmpty(stopId)) {
                cr.put("stopInfo", mediaStopService.getMediaStopInfo(stopId));
            }
        }

        return result;
    }

    public void downloadModel(ReqBean reqBean, HttpServletRequest request, HttpServletResponse response)
            throws Exception {
        String mediaId = reqBean.getInfo("mediaId");
        if (StringUtil.isEmpty(mediaId)) {
            throw new BusinessException("参数错误!");
        }
        MediaType media = mediaTypeService.getMediaType(reqBean.getInfo("mediaId"));
        OutputStream os = response.getOutputStream();
        try {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.createSheet("合同资源明细模版");
            // 在第一行第一个单元格，插入下拉框
            HSSFRow row = sheet.createRow(0);
            row.createCell(0).setCellValue("资源编号");
            String typeName = "";
            if (Constant.MEDIA_TYPE_BUS.equals(media.getParentId())) {
                typeName = "车辆";
                row.createCell(1).setCellValue("自编号");
                row.createCell(2).setCellValue("车牌号");
                row.createCell(3).setCellValue("线路");
                row.createCell(4).setCellValue("经营开始日期");
                row.createCell(5).setCellValue("经营截止日期");
            } else if (Constant.MEDIA_TYPE_STOP.equals(media.getParentId())) {
                typeName = "站点";
                row.createCell(1).setCellValue("站点名称");
                row.createCell(2).setCellValue("街道");
                row.createCell(3).setCellValue("行向");
                row.createCell(4).setCellValue("经营开始日期");
                row.createCell(5).setCellValue("经营截止日期");
            } else {
            }

            response.reset();
            response.setContentType("application/vnd.ms-excel");
            String filename = URLEncoder.encode("合同资源明细模板-" + typeName + ".xls", "UTF-8");
            response.addHeader("Content-Disposition", "attachment;filename=" + filename);
            wb.write(os);
            os.close();
        } catch (IOException e) {
            throw new BusinessException(" export data error!" + e.getMessage());
        } finally {
            os.close();
        }
    }

    public void contractExcel(String contractId, String excelPath, String mediaId) {
        if (StringUtils.isBlank(excelPath) || StringUtils.isBlank(contractId)) {
            throw new BusinessException("缺少参数!");
        }
        MediaType media = null;
        if (!StringUtil.isEmpty(mediaId)) {
            media = mediaTypeService.getMediaType(mediaId);
        }

        String filePath = Constants.ATTACHMENT_PATH + File.separator + excelPath;
        ExcelReader excel = new ExcelReader(filePath);

        int row = excel.getRowNum(0);

        if (excel == null || row == 0) {
            throw new BusinessException("导入的数据为空!");
        }

        List<String> errMsgs = new ArrayList<String>();

        List<String[]> dataList = excel.getAllData(0);
        List<ExcelContractResource> originalList = new ArrayList<ExcelContractResource>();
        for (int i = 1; i <= row; i++) {
            if (i > dataList.size()) {
                break;
            }
            int rowNo = i + 1;
            String resourceCode = dataList.get(i)[0];

            if (StringUtils.isBlank(resourceCode)) {
                errMsgs.add("[row:" + rowNo + "]资源编号不能为空");
                continue;
            }
            MediaResource resource = mediaResourceMapper.selectByResourceCode(resourceCode);
            if (resource == null) {
                errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]编号不存在!");
                continue;
            }

            String startDate = "";
            String endDate = "";
            if (media != null) {
                if (!media.getMediaId().equals(resource.getMediaType())) {
                    MediaType _media = mediaTypeService.getMediaType(resource.getMediaType());
                    errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]媒体形式不符合(" + _media.getMediaName() + ")!");
                    continue;
                }
                if (Constant.MEDIA_TYPE_BUS.equals(media.getParentId())) {
                    // String vehicleNo = dataList.get(i)[1];
                    // String plateNo = dataList.get(i)[1];
                    // String lineNo = dataList.get(i)[1];
                    startDate = dataList.get(i)[4];
                    endDate = dataList.get(i)[5];
                } else if (Constant.MEDIA_TYPE_STOP.equals(media.getParentId())) {
                    // String stopName = dataList.get(i)[1];
                    // String roadName = dataList.get(i)[1];
                    // String direction = dataList.get(i)[1];
                    startDate = dataList.get(i)[4];
                    endDate = dataList.get(i)[5];
                } else {
                }
            } else {
                startDate = dataList.get(i)[8];
                endDate = dataList.get(i)[9];
            }

            Date startTime = DateUtil.formatExcelDate(startDate, "EEE MMM dd HH:mm:ss z yyyy", "yyyy-MM-dd");
            Date endTime = DateUtil.formatExcelDate(endDate, "EEE MMM dd HH:mm:ss z yyyy", "yyyy-MM-dd");
            if (startTime.after(endTime)) {
                errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]经营日期不正确!");
                continue;
            }

            // 验证excel数据日期 ..........
            boolean excelDupli = false;
            for (ExcelContractResource r : originalList) {
                if (r.getResourceId().equals(resource.getResourceId())
                        && !(startTime.after(r.getEndDate()) || endTime.before(r.getStartDate()))) {
                    errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]经营日期重复[row:" + r.getRowNo() + "]!");
                    excelDupli = true;
                    break;
                }
            }
            if (excelDupli) {
                continue;
            }

            // 验证数据库数据日期 ..........
            boolean dbDupli = false;
            List<ContractResource> _l = contractResourceMapper.selectByContractIdAndResourceId(contractId,
                    resource.getResourceId());
            if (_l != null && !_l.isEmpty()) {
                for (ContractResource r : _l) {
                    if (r.getResourceId().equals(resource.getResourceId())
                            && !(startTime.after(r.getEndDate()) || endTime.before(r.getStartDate()))) {
                        errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]" + "经营日期重复["
                                + (DateUtil.toString(r.getStartDate()) + '-') + DateUtil.toString(r.getEndDate())
                                + "]!");
                        dbDupli = true;
                        break;
                    }
                }
            }
            if (dbDupli) {
                continue;
            }

            // 验证其它合同资源日期 ..........
            String sDate = DateUtil.toString(startTime);
            String eDate = DateUtil.toString(endTime);
            StringBuffer sql = new StringBuffer("select c.CONTRACT_NO from AD_CONTRACT c, AD_CONTRACT_RESOURCE r ");
            sql.append(" where c.CONTRACT_ID = r.CONTRACT_ID");
            sql.append(" and r.RESOURCE_ID = '").append(resource.getResourceId()).append("'");
            sql.append(" and ( ");
            sql.append(" ( r.START_DATE >= '").append(sDate).append("' and r.START_DATE <= '").append(eDate)
                    .append("')");
            sql.append(" or ( r.END_DATE >= '").append(sDate).append("' and r.END_DATE <= '").append(eDate)
                    .append("')");
            sql.append(" or ( r.START_DATE <= '").append(sDate).append("' and r.END_DATE >= '").append(eDate)
                    .append("')");
            sql.append(" )");
            JSONArray array = this.query(sql.toString());
            if (array != null && array.size() > 0) {
                errMsgs.add("[row:" + rowNo + "][资源编号:" + resourceCode + "]" + "经营日期重复[合同:"
                        + array.getJSONObject(0).getString("contractNo") + "]");
                continue;
            }

            ExcelContractResource contractResource = new ExcelContractResource();
            contractResource.setContractId(contractId);
            contractResource.setResourceId(resource.getResourceId());
            contractResource.setStartDate(startTime);
            contractResource.setEndDate(endTime);
            contractResource.setRowNo(rowNo);
            originalList.add(contractResource);
        }

        if (!errMsgs.isEmpty()) {
            throw new BusinessException("数据导入错误!", errMsgs);
        }

        for (int i = 0; i < originalList.size(); i++) {
            ContractResource contractResource = originalList.get(i);
            contractResource.setId(intIncrementId("AD_CONTRACT_RESOURCE", "ID"));
            contractResource.setResourceId(mediaResourceMapper.selectByResourceCode(contractResource.getResourceId())
                    .getResourceId());
            contractResourceMapper.insert(contractResource);
        }
    }

    public JSONObject mediaResources(ReqBean reqBean) {
        String contractId = reqBean.getInfo("contractId");
        String mediaType = reqBean.getInfo("mediaType");
        if (StringUtils.isBlank(contractId)) {
            throw new BusinessException("缺少参数!");
        }

        StringBuffer sql = new StringBuffer("SELECT a.*,CONCAT(a.START_DATE,' 至 ',a.END_DATE) start2EndDate");
        sql.append(" FROM AD_CONTRACT_RESOURCE a ");
        sql.append(" LEFT JOIN MEDIA_RESOURCE r on a.RESOURCE_ID = r.RESOURCE_ID");

        sql.append(" where a.CONTRACT_ID = '" + contractId + "'");
        sql.append(" and r.MEDIA_TYPE = '" + mediaType + "'");
        sql.append(" order by r.BUS_ID,r.STOP_ID, r.MEDIA_TYPE ");
        JSONObject returnJson = queryTable(new SqlBuilder(sql.toString()), reqBean.getPageSize(),
                reqBean.getPageIndex());
        JSONArray result = new JSONArray();
        if (returnJson != null && returnJson.getJSONArray("rows") != null && returnJson.getJSONArray("rows").size() > 0) {
            result = returnJson.getJSONArray("rows");
            for (int i = 0; i < result.size(); i++) {
                JSONObject cr = result.getJSONObject(i);
                String resourceId = cr.getString("resourceId");
                MediaResource resource = mediaResourceService.getMediaResource(resourceId);
                cr.put("resourceInfo", resource.toJsonObject());
                String busId = resource.getBusId();
                String stopId = resource.getStopId();
                if (!StringUtil.isEmpty(busId)) {
                    cr.put("busInfo", mediaBusService.getMediaBusInfo(busId));
                } else if (!StringUtil.isEmpty(stopId)) {
                    cr.put("stopInfo", mediaStopService.getMediaStopInfo(stopId));
                }
            }
        }
        return returnJson;
    }

    public JSONObject mediaTypeCount(ReqBean reqBean) {

        String parentId = reqBean.getInfo("parentId");
        String contractId = reqBean.getInfo("contractId");
        if (StringUtils.isBlank(parentId) || StringUtils.isBlank(contractId)) {
            throw new BusinessException("缺少参数!");
        }
        JSONObject countJson = new JSONObject();

        JSONArray mediaTypes = mediaTypeService.getMediaTypesByParentId(reqBean);
        for (int i = 0; i < mediaTypes.size(); i++) {
            JSONObject data = mediaTypes.getJSONObject(i);
            String sql = "SELECT count(1) FROM AD_CONTRACT_RESOURCE a "
                    + "LEFT JOIN MEDIA_RESOURCE r on a.RESOURCE_ID = r.RESOURCE_ID where a.CONTRACT_ID = '"
                    + contractId + "' " + "and r.MEDIA_TYPE = '" + data.getString("value") + "'";
            int count = this.executeInt(sql);
            countJson.put(data.getString("value"), count);
        }

        return countJson;
    }

    public JSONArray contractTree(ReqBean reqBean) {
        String resourceId = reqBean.getInfo("resourceId");
        String contractId = reqBean.getInfo("contractId");
        if(StringUtils.isBlank(resourceId)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "SELECT CONCAT(c.CUSTOMER_NAME,'-',a.CONTRACT_NO) name ,a.CONTRACT_ID value ,a.* "
                + "FROM AD_CONTRACT a LEFT JOIN AD_CUSTOMER c ON c.CUSTOMER_ID = a.CUSTOMER_ID "
                + "where a.CONTRACT_ID NOT IN (select cr.CONTRACT_ID FROM AD_CONTRACT_RESOURCE cr "
                + "where cr.RESOURCE_ID = '" + resourceId + "'";
        if(StringUtils.isNotBlank(contractId)) {
            sql = sql + " and cr.CONTRACT_ID <> '"+contractId+"'";
        }
        sql = sql + " GROUP BY cr.CONTRACT_ID)";

        return query(sql);
    }

    public void addContractResource(ReqBean reqBean) {
        String resourceId = reqBean.getInfo("resourceId");
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");
        String contractId = reqBean.getInfo("contractId");
        String price = reqBean.getInfo("price");
        if (StringUtils.isBlank(resourceId) || StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate)
                || StringUtils.isBlank(contractId) || StringUtils.isBlank(price)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "select c.*,cr.ID,cr.RESOURCE_ID,cr.START_DATE,cr.END_DATE FROM AD_CONTRACT c LEFT JOIN AD_CONTRACT_RESOURCE cr ON c.CONTRACT_ID = cr.CONTRACT_ID "
                + "where cr.RESOURCE_ID = '"
                + resourceId
                + "' AND cr.START_DATE <= '"
                + endDate
                + "' AND cr.END_DATE >= '" + startDate + "'";

        JSONArray array = query(sql);
        if (array != null && array.size() > 0) {
            throw new BusinessException("合同起止时间存在冲突!");
        } else { // 添加合同明细
            ContractResource contractResource = new ContractResource();
            contractResource.setId(intIncrementId("AD_CONTRACT_RESOURCE", "ID"));
            contractResource.setResourceId(resourceId);
            contractResource.setContractId(contractId);
            contractResource.setStartDate(DateUtil.parse(startDate));
            contractResource.setEndDate(DateUtil.parse(endDate));
            contractResource.setPrice(Double.parseDouble(price));
            contractResourceMapper.insert(contractResource);
        }

    }

    public void updateContractResource(ReqBean reqBean) {
        String resourceId = reqBean.getInfo("resourceId");
        String startDate = reqBean.getInfo("startDate");
        String endDate = reqBean.getInfo("endDate");
        String contractId = reqBean.getInfo("contractId");
        String price = reqBean.getInfo("price");
        String id = reqBean.getInfo("id");
        if (StringUtils.isBlank(resourceId) || StringUtils.isBlank(startDate) || StringUtils.isBlank(endDate)
                || StringUtils.isBlank(contractId) || StringUtils.isBlank(price)) {
            throw new BusinessException("缺少参数!");
        }

        String sql = "select c.*,cr.ID,cr.RESOURCE_ID,cr.START_DATE,cr.END_DATE FROM AD_CONTRACT c LEFT JOIN AD_CONTRACT_RESOURCE cr ON c.CONTRACT_ID = cr.CONTRACT_ID "
                + "where cr.RESOURCE_ID = '"
                + resourceId
                + "' AND cr.START_DATE <= '"
                + endDate
                + "' AND cr.END_DATE >= '" + startDate + "' AND cr.ID <> '" + id + "'";

        JSONArray array = query(sql);
        if (array != null && array.size() > 0) {
            throw new BusinessException("合同起止时间存在冲突!");
        } else { // 添加合同明细
            ContractResource contractResource = contractResourceMapper.selectByPrimaryKey(id);
            contractResource.setResourceId(resourceId);
            contractResource.setContractId(contractId);
            contractResource.setStartDate(DateUtil.parse(startDate));
            contractResource.setEndDate(DateUtil.parse(endDate));
            contractResource.setPrice(Double.parseDouble(price));
            contractResourceMapper.updateByPrimaryKeySelective(contractResource);
        }
    }
}

class ExcelContractResource extends ContractResource {

    private static final long serialVersionUID = 8550976649825430741L;

    private int rowNo;

    public int getRowNo() {
        return rowNo;
    }

    public void setRowNo(int rowNo) {
        this.rowNo = rowNo;
    }
}